/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Control;

import Model.*;
import Model.Types;
import Model.User;
import db.DBConnector;
import java.sql.*;
import java.text.DecimalFormat;
import java.util.ArrayList;
import javax.servlet.ServletContext;

/**
 * The data Manager is responsible for fetching information from the database
 * to the java model class
 * @author Jayson Kiong
 */
public class DataManager {
    private static ServletContext context;
    private static String MESSAGE_INBOX = "Inbox";
    private static String MESSAGE_SENT = "Sent";
    private static String MESSAGE_TRASH = "Trash";

    public void setServletContext(ServletContext context){
        this.context = context;
    }
    
    public User getAnswer(int userID){
        Connection con = DBConnector.connect();
        PreparedStatement ps;
        ResultSet rs;
        User user = new User();
        String query = "SELECT userID,sec_ans_1,sec_ans_2 "
                     + " FROM quicklenddb.user"
                     + " WHERE userID = \"" +userID+ "\" and status=1;";
        
        if(con != null){
            try {
                ps = con.prepareStatement(query);
                rs = ps.executeQuery();

                while(rs.next()){
                    user.setiUserid(rs.getInt("userID"));
                    user.setStrAnswer1(rs.getString("sec_ans_1"));
                    user.setStrAnswer2(rs.getString("sec_ans_2"));
                    
                }

                
                ps.close();
                rs.close();
                con.close();
                
            }catch(SQLException ex) {
                System.err.println("Exception: " + ex.getMessage());
            }
        }
        
        return user;
        
    }
    
    public User getUserData(String username, String password){
        

        Connection con = DBConnector.connect();
        PreparedStatement ps;
        ResultSet rs;
        User user = new User();
        
        String query = "SELECT * "
                     + " FROM user"
                     + " WHERE username = \"" +username+ "\" and password = md5('"+password+"') and status=1;";
         
       
        if(con != null){
            try {
                ps = con.prepareStatement(query);
                rs = ps.executeQuery();

                while(rs.next()){
                    user.setiUserid(rs.getInt("userID"));
                    user.setStrUsername(rs.getString("username"));
                    user.setStrPassword(rs.getString("password"));
                    user.setStrQuestion1(rs.getString("sec_quest_1"));
                    user.setStrAnswer1(rs.getString("sec_ans_1"));
                    user.setStrQuestion2(rs.getString("sec_quest_2"));
                    user.setStrAnswer2(rs.getString("sec_ans_2"));
                    user.setStrLastname(rs.getString("lastname"));
                    user.setStrFirstname(rs.getString("firstname"));
                    user.setStrMiddlename(rs.getString("middlename"));
                    user.setStrContactno(rs.getString("contact"));
                }

                
                ps.close();
                rs.close();
                con.close();
                
            }catch(SQLException ex) {
                System.err.println("Exception: " + ex.getMessage());
            }
        }
        return user;
    }
    
    public void resetPassword(int userID, String password){
       

        Connection con = DBConnector.connect();
        PreparedStatement ps;
        ResultSet rs;
        
        String query = "UPDATE quicklenddb.user"
                     + " SET password=md5('"+password+"')"
                     + " WHERE userID = \"" +userID+ "\";";
         

        if(con != null){

            try {
                ps = con.prepareStatement(query);
                ps.executeUpdate();
                ps.close();
                con.close();
                
            }catch(SQLException ex) {
                System.err.println("Exception: " + ex.getMessage());
            }
        }
    }
    
    public User getSecretQuestion(String username){
        
       

        Connection con = DBConnector.connect();
        PreparedStatement ps;
        ResultSet rs;
        User user = new User();
        
        String query = "SELECT userID,username,sec_quest_1, sec_quest_2 "
                     + " FROM user"
                     + " WHERE username = \"" +username+ "\"";
         

        if(con != null){

            try {
                ps = con.prepareStatement(query);
                rs = ps.executeQuery();

                while(rs.next()){
                    user.setiUserid(rs.getInt("userID"));
                    user.setStrUsername(rs.getString("username"));
                    user.setStrQuestion1(rs.getString("sec_quest_1"));
                    user.setStrQuestion2(rs.getString("sec_quest_2"));
                }

               
                ps.close();
                rs.close();
                con.close();
                
            }catch(SQLException ex) {
                System.err.println("Exception: " + ex.getMessage());
            }
        }
        return user;
    }
    
    public ArrayList getBankList(){
        ArrayList ar =new ArrayList();
        Connection con = DBConnector.connect();
        PreparedStatement ps;
        ResultSet rs;
        
        String query = "SELECT * "
                     + "FROM quicklenddb.bank";
         

        if(con != null){

            try {
                ps = con.prepareStatement(query);
                rs = ps.executeQuery();
                
                while(rs.next()){
                    String bankname="";
                    bankname = rs.getString("bank");
                    ar.add(bankname);
                }     
                ps.close();
                rs.close();
                con.close();
                
            }catch(Exception ex) {
                System.err.println("Exception: " + ex);
            }
        }
        return ar;
    }
    
    public ArrayList getTypes(){
        
        ArrayList ar =new ArrayList();
        Connection con = DBConnector.connect();
        PreparedStatement ps;
        ResultSet rs;
        
        String query = "SELECT * "
                     + "FROM quicklenddb.type";
         

        if(con != null){

            try {
                ps = con.prepareStatement(query);
                rs = ps.executeQuery();
                
                while(rs.next()){
                    Types type = new Types();
                    
                    type.setStrName(rs.getString("name"));
                    type.setStrType(rs.getString("type"));  
                    type.setiID(rs.getInt("typeID"));
                    ar.add(type);
                }     
                ps.close();
                rs.close();
                con.close();
                
            }catch(Exception ex) {
                System.err.println("Exception: " + ex);
            }
        }
        return ar;
    }
    public Agent getAgentInfo(int id){
        Connection con = DBConnector.connect();
        PreparedStatement ps;
        ResultSet rs;
        Agent agent = new Agent();
        
        String query = "SELECT * "
                     + " FROM agent"
                     + " WHERE AgentID = '"+id+"'";
         

        if(con != null){

            try {
                ps = con.prepareStatement(query);
                rs = ps.executeQuery();

                while(rs.next()){
                    agent.setiID(rs.getInt("AgentID"));
                    agent.setStrAgentcode(rs.getString("agentcode"));
                    agent.setStrName(rs.getString("a_name"));
                    agent.setStrContact(rs.getString("a_contact"));
                    agent.setStrEmail(rs.getString("a_email"));
                    agent.setStrStreet(rs.getString("a_street"));
                    agent.setStrCity(rs.getString("a_city"));
                    agent.setiZip(rs.getInt("a_zip"));
                    agent.setStrRemarks(rs.getString("a_remarks"));
                    agent.setfCommission(rs.getFloat("commission"));
                }

                ps.close();
                rs.close();
                con.close();
                
            }catch(SQLException ex) {
                System.err.println("Exception: " + ex.getMessage());
            }
        }
        return agent;
    }
    public ArrayList getAgentList(){
    
        ArrayList ar =new ArrayList();
        Connection con = DBConnector.connect();
        PreparedStatement ps;
        ResultSet rs;
        
        String query = "SELECT * "
                     + " FROM quicklenddb.agent";
        
        if(con != null){

            try {
                ps = con.prepareStatement(query);
                rs = ps.executeQuery();

                while(rs.next()){
                    Agent agent = new Agent();
                    agent.setiID(rs.getInt("AgentID"));
                    agent.setStrAgentcode(rs.getString("agentcode"));
                    agent.setStrName(rs.getString("a_name"));
                    agent.setStrContact(rs.getString("a_contact"));
                    agent.setStrEmail(rs.getString("a_email"));
                    agent.setStrStreet(rs.getString("a_street"));
                    agent.setStrCity(rs.getString("a_city"));
                    agent.setiZip(rs.getInt("a_zip"));
                    agent.setStrRemarks(rs.getString("a_remarks"));
                    agent.setfCommission(rs.getFloat("commission"));
                    ar.add(agent);
                }

                
                ps.close();
                rs.close();
                con.close();
                
            }catch(SQLException ex) {
                System.err.println("Exception: " + ex.getMessage());
            }
        }
        
        return ar;
    }
    
     public ArrayList getUserList(){
    
        ArrayList ar =new ArrayList();
        Connection con = DBConnector.connect();
        PreparedStatement ps;
        ResultSet rs;
        
        String query = "SELECT * "
                     + "FROM quicklenddb.user "
                     + "WHERE status=0";
        
        if(con != null){

            try {
                ps = con.prepareStatement(query);
                rs = ps.executeQuery();

                while(rs.next()){
                    User user = new User();
                    user.setiUserid(rs.getInt("userID"));
                    user.setStrLastname(rs.getString("lastname"));
                    user.setStrFirstname(rs.getString("firstname"));
                    user.setStrMiddlename(rs.getString("middlename"));
                    ar.add(user);
                }

                
                ps.close();
                rs.close();
                con.close();
                
            }catch(SQLException ex) {
                System.err.println("Exception: " + ex.getMessage());
            }
        }
        
        return ar;
    }
    
    public void deleteType(String id){
        Connection con = DBConnector.connect();
        PreparedStatement ps;
        ResultSet rs;
        
        String query = "DELETE from quicklenddb.type "
                     + "WHERE typeID="+id;
        
        if(con != null){

            try {
                ps = con.prepareStatement(query);
                ps.executeUpdate();
                ps.close();
                con.close();
                
            }catch(SQLException ex) {
                System.err.println("Exception: " + ex.getMessage());
            }
        }
    }
    
    public ArrayList getPrecompList(){
        ArrayList ar =new ArrayList();
        Connection con = DBConnector.connect();
        PreparedStatement ps;
        ResultSet rs;
        
        String query = "SELECT * "
                     + " FROM quicklenddb.precomp";
        
        if(con != null){

            try {
                ps = con.prepareStatement(query);
                rs = ps.executeQuery();

                while(rs.next()){
                    Precomputation precomp = new Precomputation();
                    precomp.setPrecompno(rs.getInt("precompno"));
                    precomp.setClienttype(rs.getString("clienttype"));
                    precomp.setSubtype(rs.getString("subtype"));
                    precomp.setLoantype(rs.getString("loantype"));
                    precomp.setPnmonth(rs.getFloat("pnmonth"));
                    precomp.setTerms(rs.getInt("terms"));
                    precomp.setUi(rs.getFloat("ui"));
                    precomp.setService(rs.getFloat("service"));
                    precomp.setEvat(rs.getFloat("evat"));
                    precomp.setDoc(rs.getFloat("doc"));
                    precomp.setCollection(rs.getFloat("collection"));
                    precomp.setProcess(rs.getFloat("process"));
                    precomp.setLastname(rs.getString("lastname"));
                    precomp.setFirstname(rs.getString("firstname"));
                    precomp.setMiddlename(rs.getString("middlename"));
                    
                    ar.add(precomp);
                }

                
                ps.close();
                rs.close();
                con.close();
                
            }catch(SQLException ex) {
                System.err.println("Exception: " + ex.getMessage());
            }
        }
        
        return ar;
    }
    
    public ArrayList getLoanList(){
        ArrayList ar =new ArrayList();
        Connection con = DBConnector.connect();
        PreparedStatement ps;
        ResultSet rs;
        
        String query = "SELECT `loan`.`loanID`, `loan`.`clientID`, `loan`.`termsID`, `loan`.`loan_amount`, `loan`.`loan_date`, `loan`.`collect_date`, `loan`.`released_date`, `loan`.`unrelease_date`, `loan`.`uncollect_date`, `loan`.`check_date`, `loan`.`bank_name`, `loan`.`checkname`, `loan`.`check_no`, `loan`.`status`, `loan`.`paid` ,`loan`.`date_released`,concat(lastname,' ',firstname,' ',middlename) as clientname"
                     + " FROM quicklenddb.loan, quicklenddb.client "
                     + " WHERE loan.clientID=client.ClientID order by loan_date asc";
        
        if(con != null){
            try {
                ps = con.prepareStatement(query);
                rs = ps.executeQuery();

                while(rs.next()){
                    Loan loan = new Loan();
                    loan.setiLoanid(rs.getInt("loanID"));
                    loan.setiTermsid(rs.getInt("termsID"));
                    loan.setiClientid(rs.getInt("clientID"));
                    loan.setStrClientName(rs.getString("clientname"));
                    loan.setfLoanamount(rs.getFloat("loan_amount"));
                    loan.setStrLoandate(rs.getString("loan_date"));
                    loan.setStrCollectdate(rs.getString("collect_date"));
                    loan.setStrReleasedate(rs.getString("released_date"));
                    loan.setStrUnreleasedate(rs.getString("unrelease_date"));
                    loan.setStrUncollectdate(rs.getString("uncollect_date"));
                    loan.setStrCheckdate(rs.getString("check_date"));
                    loan.setStrBankname(rs.getString("bank_name"));
                    loan.setStrCheckname(rs.getString("checkname"));
                    loan.setStrCheckno(rs.getString("check_no"));
                    loan.setStrStatus(rs.getString("status"));
                    loan.setStrPaid(rs.getString("paid"));
                    loan.setStrDatereleased(rs.getString("date_released"));
                    ar.add(loan);
                }

                
                ps.close();
                rs.close();
                con.close();
                
            }catch(SQLException ex) {
                System.err.println("Exception: " + ex.getMessage());
            }
        }
        
        return ar;
    }
    
    public Terms getTermInfo(int termid){
        Terms term = new Terms();
        Connection con = DBConnector.connect();
        PreparedStatement ps;
        ResultSet rs;
        
        String query = "SELECT * "
                     + "FROM quicklenddb.terms "
                     + "WHERE termID="+termid;
        
        if(con != null){

            try {
                ps = con.prepareStatement(query);
                rs = ps.executeQuery();

                while(rs.next()){
                    term.setiTermid(rs.getInt("termID"));
                    term.setStrTerm(rs.getString("terms"));
                    term.setStrClienttype(rs.getString("clienttype"));
                    term.setStrLoantype(rs.getString("loantype"));
                    term.setStrSubtype(rs.getString("subtype"));
                    term.setfLessui(rs.getFloat("less_ui"));
                    term.setfServicecharge(rs.getFloat("service_charge"));
                    term.setfNotaryfee(rs.getFloat("notary_fee"));
                    term.setfEvat(rs.getFloat("evat"));
                    term.setfDocs(rs.getFloat("docs"));
                    term.setfCollectionfee(rs.getFloat("collectionfee"));
                    term.setfProcessingfee(rs.getFloat("processingfee"));
                    term.setfBankcharge(rs.getFloat("bank_charge"));
                    term.setfLbccharge(rs.getFloat("lbc_charge"));
                    term.setfCashbound(rs.getFloat("cashbound"));
                    term.setfCommission(rs.getFloat("commission"));

                }

                
                ps.close();
                rs.close();
                con.close();
                
            }catch(SQLException ex) {
                System.err.println("Exception: " + ex.getMessage());
            }
        }
        
        return term;
    }
    public ArrayList getTermList(){
        ArrayList ar =new ArrayList();
        Connection con = DBConnector.connect();
        DecimalFormat df = new DecimalFormat("0.00");
        PreparedStatement ps;
        ResultSet rs;
        
        String query = "SELECT * "
                     + " FROM quicklenddb.terms";
        
        if(con != null){

            try {
                ps = con.prepareStatement(query);
                rs = ps.executeQuery();

                while(rs.next()){
                    Terms term = new Terms();
                    term.setiTermid(rs.getInt("termID"));
                    term.setStrTerm(rs.getString("terms"));
                    term.setStrClienttype(rs.getString("clienttype"));
                    term.setStrLoantype(rs.getString("loantype"));
                    term.setStrSubtype(rs.getString("subtype"));
                    term.setfLessui(rs.getFloat("less_ui"));
                    term.setfServicecharge(rs.getFloat("service_charge"));
                    term.setfNotaryfee(rs.getFloat("notary_fee"));
                    term.setfEvat(rs.getFloat("evat"));
                    term.setfDocs(rs.getFloat("docs"));
                    term.setfCollectionfee(rs.getFloat("collectionfee"));
                    term.setfProcessingfee(rs.getFloat("processingfee"));
                    term.setfBankcharge(rs.getFloat("bank_charge"));
                    term.setfLbccharge(rs.getFloat("lbc_charge"));
                    System.out.println(term.getfLbccharge());
                    term.setfCashbound(rs.getFloat("cashbound"));
                    term.setfCommission(rs.getFloat("commission"));
                    ar.add(term);
                }

                
                ps.close();
                rs.close();
                con.close();
                
            }catch(SQLException ex) {
                System.err.println("Exception: " + ex.getMessage());
            }
        }
        
        return ar;
    }
    
    public ArrayList getSoaList(int clientid){
        ArrayList ar =new ArrayList();
        Connection con = DBConnector.connect();
        PreparedStatement ps;
        ResultSet rs;
        
        String query = "SELECT `soa`.`soaID`,`soa`.`clientID`,`soa`.`start_date`,`soa`.`monthly`,`soa`.`duedate`,"
                       +"`soa`.`amountdue`,`soa`.`collection`,`soa`.`comakerpay`,`soa`.`fc`,`soa`.`cashpay`,`soa`.`refund`,`soa`.`balance`,"
                       +"`soa`.`fc_percent`,`soa`.`rebill_date`,`soa`.`rebill_co_amount`,`soa`.`rebill_amount`,`soa`.`rebill_status`,`soa`.`status_date`,"
                       +"concat(client.lastname,' ',client.firstname,' ',client.middlename) as clientname,concat(comaker.c_lastname,' ',comaker.c_firstname,' ',comaker.c_mname) as comakername,terms.loantype,"
                       +"loan.pnmonth,terms.terms,loan.loan_amount,comaker.comakerID "
                       +"FROM `quicklenddb`.`soa`, quicklenddb.client, quicklenddb.comaker, quicklenddb.terms, quicklenddb.loan "
                       +"where soa.clientID="+clientid+" and client.clientID = soa.clientID and comaker.clientID = client.clientID and comaker.clientID = soa.clientID and  terms.termID = loan.termsID and loan.clientID = client.clientID";
        
        if(con != null){
           
            try {
                ps = con.prepareStatement(query);
                rs = ps.executeQuery();

                while(rs.next()){
                    Soa soa = new Soa();
                    soa.setiSoaid(rs.getInt("soaID"));
                    soa.setiClientid(rs.getInt("clientID"));
                    soa.setStrStartdate(rs.getString("start_date"));
                    soa.setfMonthly(rs.getFloat("monthly"));
                    soa.setStrDuedate(rs.getString("duedate"));
                    soa.setfAmountdue(rs.getFloat("amountdue"));
                    soa.setfCollection(rs.getFloat("collection"));
                    soa.setfComakerpay(rs.getFloat("comakerpay"));
                    soa.setfFc(rs.getFloat("fc"));
                    soa.setfCashpay(rs.getFloat("cashpay"));
                    soa.setfRefund(rs.getFloat("refund"));
                    soa.setfBalance(rs.getFloat("balance"));
                    soa.setfPercent(rs.getFloat("fc_percent"));
                    soa.setStrRebilldate(rs.getString("rebill_date"));
                    soa.setfRebillcoamount(rs.getFloat("rebill_co_amount"));
                    soa.setfRebillamount(rs.getFloat("rebill_amount"));
                    soa.setStrRebillstatus(rs.getString("rebill_status"));
                    soa.setStrStatusdate(rs.getString("status_date"));
                    soa.setStrClientname(rs.getString("clientname"));
                    soa.setStrComakername(rs.getString("comakername"));
                    soa.setfPnmonth(rs.getFloat("pnmonth"));
                    soa.setStrLoantype(rs.getString("loantype"));
                    soa.setiTerms(rs.getInt("terms"));
                    soa.setfLoanamount(rs.getFloat("loan_amount"));
                    soa.setiComakerid(rs.getInt("comakerID"));
                    soa.setfCollectionsum(getCollectionSum(clientid));
                    ar.add(soa);
                }

                
                ps.close();
                rs.close();
                con.close();
                
            }catch(SQLException ex) {
                System.err.println("Exception: " + ex.getMessage());
            }
        }
        
        return ar;
    }
    
    public float getCollectionSum(int clientid){
        float collectionsum = 0f;
        Connection con = DBConnector.connect();
        PreparedStatement ps;
        ResultSet rs;
        
        String query = "SELECT sum(soa.collection) as collectionsum "
                       +"FROM `quicklenddb`.`soa`, quicklenddb.client, quicklenddb.comaker, quicklenddb.terms, quicklenddb.loan "
                       +"where soa.clientID="+clientid+" and client.clientID = soa.clientID and comaker.clientID = client.clientID and comaker.clientID = soa.clientID and  terms.termID = loan.termsID and loan.clientID = client.clientID";
        
        if(con != null){

            try {
                ps = con.prepareStatement(query);
                rs = ps.executeQuery();

                while(rs.next()){
                    
                collectionsum = rs.getFloat("collectionsum");
                        
                }

                
                ps.close();
                rs.close();
                con.close();
                
            }catch(SQLException ex) {
                System.err.println("Exception: " + ex.getMessage());
            }
        
        
    }
    
    return collectionsum;
    
    
   } 
}

